"""
需安装的库文件：
pip install xlrd==1.2.0
pip install pywin32
换源网址：
- 豆瓣：http://pypi.douban.com/simple/
- 中科大：https://pypi.mirrors.ustc.edu.cn/simple/
- 清华：https://pypi.tuna.tsinghua.edu.cn/simple
换源安装，例如：pip install pywin32 -i https://pypi.tuna.tsinghua.edu.cn/simple
"""

import xlrd
from win32com import client
from xlrd.sheet import Sheet
import datetime


# 获取sheet
# 参数解读：get_row(excel的路径，选择的sheet名字)
def get_sheet(file_path, sheet_name):
    # 打开excel表格
    book = xlrd.open_workbook(file_path)
    # 打印excel所有子表格的名字
    form_name = book.sheet_names()
    # 获取选择子表格的位置
    sheet_index = form_name.index(sheet_name)
    # 选择子表格为操作对象
    sheet = book.sheet_by_index(sheet_index)
    # 返回的结果为列表，例如：[1，2，3，4]
    return sheet


# 获取sheet  行数
def get_row_num(sheet_obj: Sheet):
    return sheet_obj.nrows


# 获取sheet  列数
def get_col_num(sheet_obj: Sheet):
    return sheet_obj.ncols


# 读一行
# 参数解读：get_row_value(选择的sheet对象，所读行的位置)
def get_row_value(sheet_obj: Sheet, row_num):
    row_arr = sheet_obj.row_values(row_num)
    # 返回的结果为列表，例如：[1，2，3，4]
    return row_arr


# 读多行数据
# 参数解读：get_rows_value(选择的sheet对象，所读行开始的位置，所读行结束的位置)
def get_rows_value(sheet_obj: Sheet, start, end):
    # 获取多行数据
    value_library = []
    for row_location in range(int(start), int(end)+1):
        row_arr = sheet_obj.row_values(row_location)
        value_library.append(row_arr)
    return value_library


# 读全部数据
# 参数解读：get_all(选择的sheet对象)
def get_all(sheet_obj: Sheet):
    # 获取选取对象的行数
    row_num = sheet_obj.nrows
    value_library = []
    for index in range(row_num):
        row_values = sheet_obj.row_values(index)
        value_library.append(row_values)
    return value_library


# 读一列
# 参数解读：get_col_value(选择的sheet对象，读列的位置)
def get_col_value(sheet_obj: Sheet, col_num):
    cols = sheet_obj.col_values(col_num)
    return cols


# 读多列
# 参数解读：get_cols_value(选择的sheet对象，读列开始的位置，读列结束的位置)
def get_cols_value(sheet_obj: Sheet, start, end):
    final_result = []
    for i in range(start, end+1):
        cols = sheet_obj.col_values(i)
        final_result.append(cols)
    return final_result


# 写一列
# 参数解读：write_data(excel的路径，选择的sheet名字，写的内容(一维数组)，列数， 开始行
def write_col(file_path, sheet_name, value=[], col_num=0, start_row=0):
    application = client.Dispatch("Excel.Application")
    application.Visible = True
    book = application.Workbooks.Open(file_path)
    sheet = book.Worksheets(sheet_name)
    for i in range(start_row, start_row+len(value)):
        sheet.Cells(i, col_num).value = value[i-start_row]
    book.Save()
    book.Close(True)


# 写一行
# 参数解读：write_row(excel的路径，选择的sheet名字，写的内容(一维数组)，行数，开始列)
def write_row(file_path='', sheet_name='', value=[], row_num=0, start_col=0):
    application = client.Dispatch("Excel.Application")
    application.Visible = True
    book = application.Workbooks.Open(file_path)
    sheet = book.Worksheets(sheet_name)
    for col_index in range(start_col, start_col+len(value)):
        sheet.Cells(row_num, col_index).value = value[col_index-start_col]
    book.Save()
    book.Close(True)


# 写多行数据
# 参数解读：write_rows(excel的路径，选择的sheet名字，写的内容(二维数组)，开始行，开始列)
def write_rows(file_path='', sheet_name='', value=[[]], start_row=0, start_col=0):
    application = client.Dispatch("Excel.Application")
    application.Visible = True
    book = application.Workbooks.Open(file_path)
    sheet = book.Worksheets(sheet_name)
    for row_index in range(start_row, start_row+len(value)):
        row_arr = value[row_index-start_row]
        for col_index in range(start_col, start_col + len(value)):
            sheet.Cells(row_index, col_index).value = row_arr[col_index - start_col]
    book.Save()
    book.Close(True)


# 读取一维数组匹配值后几个位值
def get_one_dimensional_value_after(arr=[], target='', after=0):
    for index in range(len(arr)):
        if target == arr[index]:
            return arr[index+after]


# 读取二维数组匹配值后几个位值
def get_two_dimensional_value_after(arr=[[]], target='', after=0):
    for index in range(len(arr)):
        for i in range(len(arr[index])):
            if target == arr[index][i]:
                return arr[index][i + after]


if __name__ == '__main__':
    # "应收账款"
    # "存  货"
    # "流动资产合计:"
    # 读取资产负债表数据
    # sheet = get_sheet(r"C:\Users\admin\Desktop\table\资产负债表.xlsx", "Sheet1")
    # # 读一行
    # print(get_all(sheet))
    # #
    # sheet_values = get_all(sheet)
    # value = get_two_dimensional_value_after(sheet_values, "应收账款", after=2)
    # print(value)
    # value = get_two_dimensional_value_after(sheet_values, "存  货", after=2)
    # print(value)
    # value = get_two_dimensional_value_after(sheet_values, "流动资产合计:", after=2)
    # print(value)
    # print(get_row_num(sheet))
    # print(get_col_num(sheet))
    # 结果为：['张三', '男', 175.0, 20.0]
    # value = ['张三', '男', 175.0, 20.0, 20, 10, 30]
    # write_col(r"C:\Users\admin\Desktop\test\新建 XLSX 工作表.xlsx", "Sheet1", value, 5, 3)
    str = datetime.datetime.now().strftime('%Y%m%d')
    print(str)
